package com.xiaoyi.query;

import com.xiaoyi.condition.*;
import com.xiaoyi.equery.EQueryModel;
import com.xiaoyi.equery.ParamsModel;
import com.xiaoyi.equery.QueryResultModel;
import com.xiaoyi.io.Resources;
import com.xiaoyi.parse.GenericTokenParser;
import com.xiaoyi.parse.TokenHandler;
import com.xiaoyi.pooled.PooledDataSource;
import com.xiaoyi.type.TypeHandler;
import com.xiaoyi.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.net.MalformedURLException;
import java.sql.*;
import java.util.*;

/**
 * @author xiaoyi
 * @version 1.0.0
 * @createTime 2023-02-21 22:33:00
 */
public abstract class AbstartQuery implements Query {
    public static Logger logger = LoggerFactory.getLogger(AbstartQuery.class.getName());

    @Override
    public QueryResultModel excuteQuery(ParamsModel paramsModel, EQueryModel eQueryModel, QueryPlugin headerQueryPlugin) throws MalformedURLException {
        PooledDataSource pooledDataSource = getPooledDataSource(eQueryModel);

        // 执行之前
        SqlHolder sqlHolder = orgSql(paramsModel, eQueryModel.getSqlTemplate());
        //
        if (Objects.nonNull(headerQueryPlugin)) {
            sqlHolder = headerQueryPlugin.beforeQuery(sqlHolder, paramsModel, eQueryModel);
        }

        QueryResultModel queryResultModel = new QueryResultModel();
        if (1 == eQueryModel.getQueryType()) {
            // 普通查询
            List<Map<String, Object>> maps = analysisRowData(pooledDataSource, sqlHolder);
            queryResultModel = new QueryResultModel(maps, Long.valueOf(maps.size()));
        } else if (2 == eQueryModel.getQueryType()) {
            // 分页列表查询
            SqlHolder countSqlHolder = new SqlHolder();
            countSqlHolder.addAllParams(sqlHolder.getParamsList());
            String countSql  = orgCountSql(sqlHolder.getSql());
            countSqlHolder.setSql(countSql);
            List<Map<String, Object>> countRes = analysisRowData(pooledDataSource, countSqlHolder);
            Map<String, Object> map = countRes.get(0);

            String pageSql = orgPageSql(sqlHolder, paramsModel);
            sqlHolder.setSql(pageSql);

            List<Map<String, Object>> pageRes = analysisRowData(pooledDataSource, sqlHolder);
            queryResultModel = new QueryResultModel(pageRes, (Long) map.getOrDefault(getCountName(), 0L));
            // 组织结果
        } else if (3 == eQueryModel.getQueryType()) {
            String sql = orgLimitSql(sqlHolder, paramsModel);
            sqlHolder.setSql(sql);
            List<Map<String, Object>> maps = analysisRowData(pooledDataSource, sqlHolder);
            queryResultModel = new QueryResultModel(maps, Long.valueOf(maps.size()));
        } else if (4 == eQueryModel.getQueryType()) {
            SqlHolder sqlHolderOrderBy = orgOderBySql(sqlHolder, paramsModel);
            List<Map<String, Object>> maps = analysisRowData(pooledDataSource, sqlHolderOrderBy);
            queryResultModel = new QueryResultModel(maps, Long.valueOf(maps.size()));
        }
        // 执行后
        if (Objects.nonNull(headerQueryPlugin)) {
            queryResultModel = headerQueryPlugin.afterQuery(queryResultModel);
        }

        return queryResultModel;

    }

    // 获取连接池
    public abstract PooledDataSource getPooledDataSource(EQueryModel eQueryModel) throws MalformedURLException;

    public abstract String getCountName();

    // 解析行数据
    private static List<Map<String, Object>> analysisRowData(PooledDataSource pooledDataSource,  SqlHolder sqlHolder) {
        List<Map<String, Object>> result = new ArrayList<>();
        Connection connection = null;
        try {
            connection = pooledDataSource.getConnection();
            logger.info("executesql:{}", sqlHolder.getSql());
            PreparedStatement preparedStatement = connection.prepareStatement(sqlHolder.getSql());

            List<Object> paramsList = sqlHolder.getParamsList();


            TypeHandlerRegistry typeHandlerRegistry = new TypeHandlerRegistry();

            for (int i=0;i<paramsList.size();i++){
                Object o = paramsList.get(i);
                TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(o.getClass());
                typeHandler.setParameter(preparedStatement,i+1,o,null);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                if (columnCount > 0) {
                    Map<String, Object> item = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        Class<?> aClass = Resources.classForName(metaData.getColumnClassName(i));
                        TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(aClass);
                        item.put(metaData.getColumnName(i), typeHandler.getResult(resultSet, i));
                    }
                    result.add(item);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (Objects.nonNull(connection)) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }


    /*
     * 组织limit sql
     * @author xiaoyi
     * @date 2023/2/18 23:06
     * @param null
     * @return
     * @version v1.0
     */
    private String orgLimitSql(SqlHolder sqlHolder, ParamsModel paramsModel) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from (");
        stringBuilder.append(sqlHolder.getSql());
        stringBuilder.append(")t");
        stringBuilder.append(" limit ");
        stringBuilder.append(paramsModel.getLimitSize());
        return stringBuilder.toString();
    }

    /*
     * 组织limit sql
     * @author xiaoyi
     * @date 2023/2/18 23:06
     * @param null
     * @return
     * @version v1.0
     */
    private SqlHolder orgOderBySql(SqlHolder sqlHolder, ParamsModel paramsModel) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from (");
        stringBuilder.append(sqlHolder.getSql());
        stringBuilder.append(")t");
        stringBuilder.append(" order by ");
        stringBuilder.append("?");
        sqlHolder.addParams(paramsModel.getOrderBy());
        return sqlHolder;
    }

    /*
     * 组织分页的sql
     * @author xiaoyi
     * @date 2023/2/18 23:06
     * @param null
     * @return
     * @version v1.0
     */
    private String orgPageSql( SqlHolder sqlHolder, ParamsModel paramsModel) {
        Integer size = paramsModel.getSize();
        if (size <= 0) {
            size = 10;
        }
        Integer page = paramsModel.getPage();
        if (page <= 0) {
            page = 1;
        }
        int index = size * (page - 1);
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from (");
        stringBuilder.append(sqlHolder.getSql());
        stringBuilder.append(")t");
        stringBuilder.append(" limit ");
        stringBuilder.append(index);
        stringBuilder.append(" , ");
        stringBuilder.append(size);
        stringBuilder.append(" ");
        String pageSql = stringBuilder.toString();
        System.out.println(pageSql);
        return pageSql;
    }

    /*
     * 组织countsql
     * @author xiaoyi
     * @date 2023/2/18 23:06
     * @param null
     * @return
     * @version v1.0
     */
    private String orgCountSql(String sql) {
        StringBuilder countSqlBuild = new StringBuilder();
        countSqlBuild.append("select count(*) as count from  (");
        countSqlBuild.append(sql);
        countSqlBuild.append(")t");
        return countSqlBuild.toString();
    }

    /*
     * 替换sql中的where里面的sql
     * @author xiaoyi
     * @date 2023/2/18 23:07
     * @param null
     * @return
     * @version v1.0
     */
    public SqlHolder  orgSql(ParamsModel paramsModel, String sql) {
        SqlHolder sqlHolder = new SqlHolder();
        Map<String, Object> whereCondition = paramsModel.getWhereCondition();
        Map<String, Object> likeConditionMap = paramsModel.getLikeCondition();
        Map<String, Object> eqConditionMap = paramsModel.getEqCondition();
        Map<String, Object> unqConditionMap = paramsModel.getUnqCondition();
        Map<String, Object> geConditionMap = paramsModel.getGeCondition();
        Map<String, Object> gtConditionMap = paramsModel.getGtCondition();
        Map<String, Object> inConditionMap = paramsModel.getInCondition();
        Map<String, Object> leConditionMap = paramsModel.getLeCondition();
        Map<String, Object> ltConditionMap = paramsModel.getLtCondition();
        Map<String, Object> betweenAndConditionMap = paramsModel.getBetweenAndCondition();
        TypeHandlerRegistry typeHandlerRegistry = new TypeHandlerRegistry();
        HashMap<String, String> map = new HashMap<>();

        List<Object> paramsList = new ArrayList<>();
        sqlHolder.setParamsList(paramsList);

        // whereCondition 走的是直接替换，会存在sql注入问题，应尽可能的少用
        LinkedHashMap<String,Object> whereConditionMap = new LinkedHashMap<>();
        whereCondition.forEach((k, v) -> {
            if (Objects.nonNull(v)){
                map.put(k, String.valueOf(v));
            }
        });

        Condition likeCondition = new LikeContition();
        ConditionModel likeConditionModel = likeCondition.condition(likeConditionMap);
        map.put("likeCondition", likeConditionModel.getSql());

        Condition eqCondition = new EqContition();
        ConditionModel eqConditionModel = eqCondition.condition(eqConditionMap);
        map.put("eqCondition", eqConditionModel.getSql());

        Condition unqCondition = new UnqContition();
        ConditionModel unqConditionModel = unqCondition.condition(unqConditionMap);
        map.put("unqCondition", unqConditionModel.getSql());

        Condition geCondition = new GeCondition();
        ConditionModel geConditionModel = geCondition.condition(geConditionMap);
        map.put("geCondition", geConditionModel.getSql());

        Condition gtCondition = new GtCondition();
        ConditionModel gtConditionModel = gtCondition.condition(gtConditionMap);
        map.put("gtCondition", gtConditionModel.getSql());

        Condition inCondition = new InContition();
        ConditionModel inConditionModel = inCondition.condition(inConditionMap);
        map.put("inCondition", inConditionModel.getSql());

        Condition leCondition = new LeCondition();
        ConditionModel leConditionModel = leCondition.condition(leConditionMap);
        map.put("leCondition", leConditionModel.getSql());

        Condition ltCondition = new LtCondition();
        ConditionModel ltConditionModel = ltCondition.condition(ltConditionMap);
        map.put("ltCondition", ltConditionModel.getSql());

        Condition betweenAndCondition = new BetweenAndConditionContition();
        ConditionModel betweenAndConditionModel = betweenAndCondition.condition(betweenAndConditionMap);
        map.put("betweenAndCondition", betweenAndConditionModel.getSql());

        // 下面是替换sql参数
        GenericTokenParser genericTokenParser = new GenericTokenParser("${", "}", new TokenHandler() {
            @Override
            public String handleToken(String key) {
                // 替换完以后的数据和参数要匹配，然后放在最终的list里面
                if ("likeCondition".equals(key)) {
                    paramsList.addAll(likeConditionModel.getParamList());
                } else if ("eqCondition".equals(key)) {
                    paramsList.addAll(eqConditionModel.getParamList());
                } else if ("unqCondition".equals(key)) {
                    paramsList.addAll(unqConditionModel.getParamList());
                } else if ("geCondition".equals(key)) {
                    paramsList.addAll(geConditionModel.getParamList());
                } else if ("gtCondition".equals(key)) {
                    paramsList.addAll(gtConditionModel.getParamList());
                } else if ("inCondition".equals(key)) {
                    paramsList.addAll(inConditionModel.getParamList());
                } else if ("leCondition".equals(key)) {
                    paramsList.addAll(leConditionModel.getParamList());
                } else if ("ltCondition".equals(key)) {
                    paramsList.addAll(ltConditionModel.getParamList());
                } else if ("betweenAndCondition".equals(key)) {
                    paramsList.addAll(betweenAndConditionModel.getParamList());
                } else {
                    Object o = whereConditionMap.get(key);
                    if (Objects.nonNull(o)){
                        paramsList.add(o);
                    }
                }
                return map.getOrDefault(key, "");
            }
        });
        String parseSql = genericTokenParser.parse(sql);
        sqlHolder.setSql(parseSql);
        return sqlHolder;
    }


}
